const db = require('../db/dbconfig');

const getgames = async(req, res) => {
    let { keyword, pagenum, pagesize } = req.query,
        status = 200
    let total = 0;
    let sql = `select g.id,g.gamename,g.cover,gametype.typename,copyrights.copyright_name,g.publishdate,g.system,g.description from game g
    INNER JOIN gametype on g.type = gametype.id
    INNER JOIN copyrights on g.copyright = copyrights.id`,
        count_sql = `select count(*) 'total' from game`
    data = null,
        msg = '请求成功！'
    try {
        if (pagenum && pagesize) {
            if (keyword) {
                sql += ` where g.gamename like '%${keyword}%'`
                count_sql += ` where gamename like '%${keyword}%'`
            }
            sql += ` limit ${(pagenum-1) * pagesize},${pagesize}`
            data = await db.sqlconnection(sql)
            const count_result = await db.sqlconnection(count_sql)
            total = count_result[0].total
        } else {
            status = 400
            msg = '必填参数'
        }
    } catch (error) {
        msg = `请求出错了,原因:${error}`
        status = 400
    } finally {
        res.send({
            data,
            total,
            meta: {
                status,
                msg
            }
        })
    }
};

// 查询单个游戏信息
const get_onegames = async(req, res) => {
    let { id } = req.query,
        status = 200
    let sql = `select g.id,g.gamename,g.cover,gametype.typename,copyrights.copyright_name,g.publishdate,g.system,g.description from game g
    INNER JOIN gametype on g.type = gametype.id
    INNER JOIN copyrights on g.copyright = copyrights.id where g.id =${id}`,
        data = null,
        msg = '请求成功！'
    try {
        if (id && id > 0) {
            data = await db.sqlconnection(sql)
        } else {
            status = 400
            msg = '必填参数'
        }
    } catch (error) {
        msg = `请求出错了,原因:${error}`
        status = 400
    } finally {
        res.send({
            data,
            meta: {
                status,
                msg
            }
        })
    }
};

// 添加游戏信息
const addgame = async(req, res) => {
    let { gamename, cover, type, copyright, system, description, publishdate } = req.body,
        insert_sql = `insert into game( gamename, cover, type, copyright, publishdate, system, description )
        values ('${gamename}','${cover}','${type}','${copyright}','${publishdate}','${system?system:0}','${description?description:null}')`,
        msg = '添加游戏成功',
        status = 200,
        data = null
    if (gamename && cover && type && copyright && publishdate) { // 判断输入是否为空
        let select_sql = `select * from game where gamename = '${gamename}'`
        const result = await db.sqlconnection(select_sql);
        if (result.length === 0) { // 判断用户名是否已经被注册
            let add_result = await db.sqlconnection(insert_sql)
            if (add_result.affectedRows <= 0) { // 判断新增sql语句是否添加成功
                msg = '添加游戏失败'
                status = 400
            }
        } else {
            status = 400
            msg = '游戏已存在，请更换账号注册'
        }
    } else {
        status = 400
        msg = '参数必填'
    }
    res.status(status).send({
        data,
        meta: {
            data,
            status,
            msg
        }
    })
}


// 删除游戏信息
const deletegame = async(req, res) => {
    let { id } = req.body,
        sql = `delete from game where id = '${id}'`,
        msg = '删除游戏成功',
        status = 200;
    try {
        const result = await db.sqlconnection(sql)
        if (result.affectedRows <= 0) { // 根据受影响行数来判断是否删除成功
            status = 400
            msg = '删除游戏信息失败'
        }
    } catch (error) {
        status = 400
        msg = `执行出错,${error}`
    } finally {
        res.status(status).send({
            msg,
            status
        })
    }
}

// 修改游戏信息
const updategame = async(req, res) => {
    let { id, gamename, cover, type, copyright, publishdate, system, description } = req.body,
        status = 200
    msg = '游戏信息修改成功',
        update_sql = `update game set gamename = ?,cover = ?,type = ?,copyright = ?,publishdate = ?,system = ?,description = ? where id = ?`
    if (id && gamename && cover && type && copyright && publishdate) {
        const result = await db.sqlconnection(update_sql, [gamename, cover, type, copyright, publishdate, system ? system : '', description ? description : '', id])
        if (result.affectedRows <= 0) {
            status = 400
            msg = '信息修改失败'
        }
    } else {
        status = 400
        msg = '参数必填'
    }
    res.status(status).send({
        meta: {
            status,
            msg
        }
    })
}
module.exports = {
    getgames,
    addgame,
    deletegame,
    updategame,
    get_onegames
}